package com.easyui.common;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.easyui.DB.ConnectDB;
import com.easyui.DB.OperateDB;
import com.easyui.bean.Student;
import com.easyui.bean.Teacher;

public class SelTeacher {
	
	public Teacher getTeacher(String tno, String tname, String sex) {
		Teacher t = new Teacher(tno, tname, sex);
		return t;
	}
	
	/**
	 * 获取所有老师数据
	 * 返回Map类型的链表
	 * @param orderBy
	 * @param sortName
	 * @return List
	 */
	public List<Map> getAllTeachers(String orderBy, String sortName, Teacher at) {
		List<Map> initList = null;
		List<Object> plist = null;
		
System.out.println(orderBy);
System.out.println(sortName);
		
		String addSql = addWhere(at);
		String sql = "";
		if(addSql != null && !addSql.equals("")) {
			sql = "select * from teacher where 1=1 " + addSql + " order by " + sortName + " " + orderBy;
		} else {
			sql = "select * from teacher order by " + sortName + " " + orderBy;
		}
		
System.out.println(sql);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	/**
	 * 获得指定页及每页大小的老师信息并按照指定字段排序
	 * 返回Teacher类型的链表
	 * @param page
	 * @param rows
	 * @param orderBy
	 * @param sortName
	 * @return
	 */
	public List<Teacher> getSpeTeachers(List<Map> initList, int page, int rows, String orderBy, String sortName, Teacher at) {
		List<Teacher> tlist = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		if(initList != null && initList.size() > 0) {//有学生信息时
			int totalRecords = 0;
			int totalPage = 0;
			int pageSize = rows;//rows 一页显示几行
			int pageNo = page;//page 第几页
			
			totalRecords = initList.size();
			totalPage = (totalRecords + pageSize -1)/pageSize;
			if(totalPage < 1) {
				totalPage = 1;
			}
			int startPos = (pageNo -1) * pageSize;
			
			String addSql = addWhere(at);
			String sql = "";
			if(addSql != null && !addSql.equals("")) {
				sql = "select * from teacher where 1=1 " + addSql + " order by " + sortName + " " + orderBy + 
				" limit " + startPos + ", " + pageSize;
			} else {
				sql = "select * from teacher order by " + sortName + " " + orderBy + 
				" limit " + startPos + ", " + pageSize;
			}
			
System.out.println(sql);
			OperateDB.setParmList(plist);//初始化paramList
			finalList = OperateDB.excuteQuery(sql);
			
			if(finalList != null && finalList.size() > 0) {//有学生信息时
				tlist = new ArrayList<Teacher>();
				for(int i=0; i<finalList.size(); i++) {
					Map map = finalList.get(i);
					Teacher t = new Teacher();
					t.init(map);
					tlist.add(t);
				}
			}
		}
		
		return tlist;
	}
	
	/**
	 * admin添加教师用户
	 * @param tno
	 * @param tname
	 * @param sex
	 * @return
	 */
	public boolean addTeacher(String tno, String tname, String sex) {
		boolean success = true;
		
		Connection conn = ConnectDB.getConnection();
		boolean autoCommit = false;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);// 取消自动提交
			
			if(insertTeacher(conn, tno, tname, sex)) {//向teacher插入数据
				if(new UserLogin().insertUser(conn, "teacher", tno)) {//向user表插入数据
					success = true;
				} else {
					success = false;
				}
			} else {
				success = false;
			}
			
		} catch (SQLException e) {
			success = false;
			System.out.println("添加教师失败，详情如下：\n");
			e.printStackTrace();
		} catch (NumberFormatException e) {
			success = false;
			System.out.println("数据格式化异常，详情如下：\n");
			e.printStackTrace();
		} finally {
			try {
				if (success) {
					conn.commit();//手动提交
				} else {
					conn.rollback();//回滚
				}
				conn.setAutoCommit(autoCommit);// 恢复现场
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			OperateDB.close(conn);
		}
		
		System.out.println("add:" + success);
		
		return success;
	}
	
	/**
	 * 向teacher表插入老师数据
	 * @param tno
	 * @param tname
	 * @param sex
	 * @return
	 */
	public boolean insertTeacher(Connection conn, String tno, String tname, String sex) {
		List<Object> plist = null;
		boolean success = false;
		
		String	sql = "insert into teacher values(?, ?, ?)";
System.out.println(sql);

		plist = new ArrayList<Object>();	
		plist.add(tno);
		plist.add(tname);
		plist.add(sex);
		OperateDB.setParmList(plist);//初始化paramList
		
		success = OperateDB.update(conn, sql);
			
		return success;
	}
	
	/**
	 * 修改或者增加老师数据
	 * @param tno
	 * @param tname
	 * @param sex
	 * @return
	 */
	public boolean updateTeacher(String tno, String tname, String sex) {
		List<Object> plist = null;
		boolean success = false;
		
		String	sql = "update teacher set tname = ?, sex = ?  where tno = ?";

		plist = new ArrayList<Object>();	
		plist.add(tname);
		plist.add(sex);
		plist.add(tno);
		OperateDB.setParmList(plist);//初始化paramList
		
		success = OperateDB.update(sql);
			
		return success;
	}
	
	/**
	 * 删除指定tno的一组老师
	 * @param id
	 * @return
	 */
	public Map<String, Object> deleteTeacher(String[] id) {
		Map<String, Object> result = new HashMap<String, Object>();
		boolean success = true;
		String msg = "";
		
		if (id.length < 1) {
			success = false;
			msg = "未选择删除数据.";
		} else {
			Connection conn = ConnectDB.getConnection();
			boolean autoCommit = false;
			try {
				autoCommit = conn.getAutoCommit();
				conn.setAutoCommit(false);// 取消自动提交

				for (int i = 0; i < id.length; i++) {
					if(canDelTeacher(conn, id[i])) {	//是否可以删除,表project、attachment、note中是否还有信息
						if(!deleteTeacher(conn, id[i])) {//删除教师信息,包括表teacher、user、manage
							success = false;
							msg = "删除教师信息失败.";
							break; // 跳出循环
						} 
					} else {
						success = false;
						msg = "系统中还有教师信息，不能删除.";
						break; // 跳出循环
					}
				}

			} catch (SQLException e) {
				success = false;
				System.out.println("删除数据失败，详情如下：\n");
				e.printStackTrace();
			} catch (NumberFormatException e) {
				success = false;
				System.out.println("数据格式化异常，详情如下：\n");
				e.printStackTrace();
			} finally {
				try {
					if (success) {
						conn.commit();//手动提交
					} else {
						conn.rollback();//回滚
					}
					conn.setAutoCommit(autoCommit);// 恢复现场
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				OperateDB.close(conn);
			}
		}
		
		if(success) {
			msg = "删除教师信息成功";
		}
		
		result.put("success", success);
		result.put("msg", msg);

		return result;
	}
	
	/**
	 * 是否可以删除
	 * 表attachment、note中是否还有信息
	 * @param conn
	 * @param tno
	 * @return
	 */
	public boolean canDelTeacher(Connection conn, String tno) {
		List<Object> plist = null;
		boolean canDel = false;
		
		String sql = "";
		
		//project表是否有信息
		sql = "select * from project where pubno = ?";
		plist = new ArrayList<Object>();	
		plist.add(tno);
		OperateDB.setParmList(plist);//初始化paramList
		
		List<Map> projectList = null;
		projectList = OperateDB.excuteQuery(conn, sql);
		
		if(projectList != null && projectList.size() > 0) {
			canDel = false;
		} else {	
			//note表是否有相关信息
			sql = "select * from note where pubtype = ? and pubno = ?";
			plist = new ArrayList<Object>();	
			plist.add("teacher");
			plist.add(tno);
			OperateDB.setParmList(plist);//初始化paramList
			
			List<Map> noteList = null;
			noteList = OperateDB.excuteQuery(conn, sql);
			
			if(noteList != null && noteList.size() > 0) {
				canDel = false;
			} else {	//attachment表是否有相关信息
				sql = "select * from attachment where uptype = ? and upno = ?";
				plist = new ArrayList<Object>();	
				plist.add("teacher");
				plist.add(tno);
				OperateDB.setParmList(plist);//初始化paramList
				
				List<Map> attachList = null;
				attachList = OperateDB.excuteQuery(sql);
				if(attachList != null && attachList.size() > 0) {
					canDel = false;
				} else {
					canDel = true;
				}
			}
		}
			
		return canDel;
	}
	
	/**
	 * 删除指定工号的教师信息
	 * 包括表teacher、user、manage
	 * teacher表和user表
	 * @param tno
	 * @return
	 */
	public boolean deleteTeacher(Connection conn, String tno) {
		List<Object> plist = null;
		boolean success = false;
		String sql = "";
		
		//删除manage表中信息，可能没有相关信息
		sql = "delete from manage where tno = ?";
		plist = new ArrayList<Object>();	
		plist.add(tno);
		OperateDB.setParmList(plist);//初始化paramList
		
		if(!OperateDB.update(conn, sql)) {
			success = false;
		} else {
			//删除teacher表中信息
			sql = "delete from teacher where tno = ?"; 
			plist = new ArrayList<Object>();	
			plist.add(tno);
			OperateDB.setParmList(plist);//初始化paramList
			
			if(!OperateDB.update(conn, sql)) {
				success = false;
			} else {
				//删除user表中信息
				sql = "delete from user where role = ? and userID = ?";
				plist = new ArrayList<Object>();	
				plist.add("teacher");
				plist.add(tno);
				OperateDB.setParmList(plist);//初始化paramList
				
				success = OperateDB.update(conn, sql);
			}
		}
		
		return success;
	}
	
	
	/**
	 * 添加查询条件
	 * @param t
	 * @return
	 */
	public String addWhere(Teacher t) {
		String addSql = "";
		String tno = t.getTno();
		String tname = t.getTname();
		String sex = t.getSex();
		
		if(tno != null && !tno.equals("")) {
			addSql += " and tno like '%" +  tno + "%'";
		}
		if(tname != null && !tname.equals("")) {
			addSql += " and tname like '%" +  tname + "%'";
		}
		if(sex != null && !sex.equals("")) {
			addSql += " and sex = '" +  sex + " '";
		}
		
		return addSql;
	}
}
